<?php
namespace Tongcheng\Controller;
use Think\Controller;

/**
 * Class BooksBackController
 * @package Tongcheng\Controller
 *
 * 图书管理
 */
class CaiWuBackController extends CommonController {
    /**
     * 图书管理列表
     */
    public function index(){

        $model = M("Books");
        $map = array();
//        $map['wid'] = 2;//小程序id，固定2
//        $map['status'] = 1;
        // $map['is_pay'] = 1;

        $res['count'] = $model->where($map)->count();
        $page = $_GET['page']?$_GET['page']:1;
        $limit = $_GET['limit']?$_GET['limit']:10;
        $list = $model->field("tp_books.*,s.nickname,FROM_UNIXTIME(tp_books.add_time,\"%m-%d %H:%i\") time")->join("tp_member s on s.uid = tp_books.uid",'left')->where()->page("{$page},{$limit}")->order('id desc')->select();
        $res['list']= $list;
        if(IS_AJAX){
            $this->ajaxReturn($res);
        }else{
            $this->assign('res', $res);
            $this->display();
        }
    }

    /**
     * 获取充值记录
     */
    public function chongZhiJiLu(){
        $time = I('time');
        $where = '';
        $where1 = '';
        if(!empty($time) && undefined != $time){
            $times = explode('~',$time);
            $where .= ' and STR_TO_DATE(ts.create_time,"%Y-%m-%d")>= "'.$times[0].'" and STR_TO_DATE(ts.create_time,"%Y-%m-%d")<= "'.$times[1].'"';
            $where1.= ' and STR_TO_DATE(create_time,"%Y-%m-%d")>= "'.$times[0].'" and STR_TO_DATE(create_time,"%Y-%m-%d")<= "'.$times[1].'"';
        }
        $model = M("ShopOrder");
        $map = array();
//        $map['wid'] = 2;//小程序id，固定2
//        $map['status'] = 1;
         $map['type'] = 3;

        $res['count'] = $model->where('type = 3 '.$where1)->count();
        $page = $_GET['page']?$_GET['page']:1;
        $limit = $_GET['limit']?$_GET['limit']:10;
        $limits = ' limit '.($page-1) * $limit.','.$limit;
        $list = M('')->query('select *,ts.id oid,ts.status ostatus,ts.create_time ocreatetime from tp_shop_order ts left join tp_member m on m.uid = ts.uid where ts.type = 3 '.$where.' order by ts.id desc '.$limits);
        $res['list']= $list;
        //充值记录统计
        $sum = M('')->query('select sum(ifnull(ts.pay_money,0)) num from tp_shop_order ts where ts.type = 3 '.$where);
        $res['num'] = $sum[0]['num'];
        if(IS_AJAX){
            $this->ajaxReturn($res);
        }else{
            $this->assign('res', $res);
            $this->display();
        }
    }


    //导出充值记录
    public function daoChu(){
        vendor('phpexcel.PHPExcel');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');

        $objExcel->getActiveSheet()->setCellValue('A1','订单号');
        $objExcel->getActiveSheet()->setCellValue('B1','价格');
        $objExcel->getActiveSheet()->setCellValue('C1','用户昵称');
        $objExcel->getActiveSheet()->setCellValue('D1','添加时间');
        $list = M('')->query('select *,ts.id oid,ts.status ostatus,ts.create_time ocreatetime from tp_shop_order ts left join tp_member m on m.uid = ts.uid where ts.type = 3 order by ts.id desc');
        $count = count($list);//$driver 为数据库表取出的数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objExcel->getActiveSheet()->setCellValue('A'. $i,$list[$i-2]['out_trade_no']);
            $objExcel->getActiveSheet()->setCellValue('B'. $i,$list[$i-2]['price']);
            $objExcel->getActiveSheet()->setCellValue('C'. $i,$list[$i-2]['nickname']);
            $objExcel->getActiveSheet()->setCellValue('D'. $i,$list[$i-2]['ocreatetime']);
        }
        $objExcel->setActiveSheetIndex();
        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename="充值记录'.time().'.xls"');
        header('Cache-Control: max-age=0');

//        $writer = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        $this->SaveViaTempFile($objWriter);
        exit();
    }

    //导出充值记录
    public function daoChu1(){
        vendor('phpexcel.PHPExcel');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objExcel->getActiveSheet()->setCellValue('A1','订单号');
        $objExcel->getActiveSheet()->setCellValue('B1','价格');
        $objExcel->getActiveSheet()->setCellValue('C1','用户昵称');
        $objExcel->getActiveSheet()->setCellValue('D1','押金状态');
        $objExcel->getActiveSheet()->setCellValue('E1','添加时间');
        $list = M('')->query('select y.*,m.nickname,so.out_trade_no,FROM_UNIXTIME(y.add_time,"%Y-%m-%d %H:%i:%s") times from tp_yajin_log y left join tp_member m on m.uid = y.uid left join tp_shop_order so on so.id = y.oid  order by y.id desc');
        $count = count($list);//$driver 为数据库表取出的数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objExcel->getActiveSheet()->setCellValue('A'. $i,$list[$i-2]['out_trade_no']);
            $objExcel->getActiveSheet()->setCellValue('B'. $i,$list[$i-2]['amount']);
            $objExcel->getActiveSheet()->setCellValue('C'. $i,$list[$i-2]['nickname']);
            $typename = '';
            switch (intval($list[$i-2]['type'])){
                case 1:
                    $typename='冻结押金';
                    break;
                case 2:
                    $typename='解封押金';
                    break;
                case 3:
                    $typename='扣除押金';
                    break;
            }
            $objExcel->getActiveSheet()->setCellValue('D'. $i,$typename);
            $objExcel->getActiveSheet()->setCellValue('E'. $i,$list[$i-2]['times']);
        }
        $objExcel->setActiveSheetIndex();
        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename="押金记录'.time().'.xls"');
        header('Cache-Control: max-age=0');

//        $writer = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        $this->SaveViaTempFile($objWriter);
        exit();
    }

    /**
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * 导出平台抽成
     */
    public function daoChu2(){
        vendor('phpexcel.PHPExcel');
        $objExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objExcel->getActiveSheet()->setCellValue('A1','订单号');
        $objExcel->getActiveSheet()->setCellValue('B1','抽成费用');
        $objExcel->getActiveSheet()->setCellValue('C1','用户昵称');
        $objExcel->getActiveSheet()->setCellValue('D1','添加时间');
        $list = M('')->query('select *,ts.id oid,ts.status ostatus,ts.create_time ocreatetime from tp_shop_order ts left join tp_member m on m.uid = ts.uid where ts.pingtaifei > 0 order by ts.id desc');
        $count = count($list);//$driver 为数据库表取出的数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objExcel->getActiveSheet()->setCellValue('A'. $i,$list[$i-2]['out_trade_no']);
            $objExcel->getActiveSheet()->setCellValue('B'. $i,$list[$i-2]['pingtaifei']);
            $objExcel->getActiveSheet()->setCellValue('C'. $i,$list[$i-2]['nickname']);
            $objExcel->getActiveSheet()->setCellValue('D'. $i,$list[$i-2]['ocreatetime']);
        }
        $objExcel->setActiveSheetIndex();
        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename="平台抽成记录'.time().'.xls"');
        header('Cache-Control: max-age=0');

//        $writer = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        $this->SaveViaTempFile($objWriter);
        exit();
    }

    /**
     * 押金记录
     */
    public function yaJinLog(){
        $model = M("YajinLog");
        $map = array();
//        $map['wid'] = 2;//小程序id，固定2
//        $map['status'] = 1;
        $map['type'] = 3;

        $res['count'] = $model->where($map)->count();
        $page = $_GET['page']?$_GET['page']:1;
        $limit = $_GET['limit']?$_GET['limit']:10;
        $limits = ' limit '.($page-1) * $limit.','.$limit;
        $list = M('')->query('select y.*,m.nickname,so.out_trade_no,FROM_UNIXTIME(y.add_time,"%Y-%m-%d %H:%i:%s") times from tp_yajin_log y left join tp_member m on m.uid = y.uid left join tp_shop_order so on so.id = y.oid where 1=1 order by y.id desc '.$limits);//y.type != 1 and y.type != 2
        $res['list']= $list;
        //押金记录统计
        //所有冻结押金+所有活动押金-所有扣除押金
        $sum1 = M('')->query('select (sum(ifnull(ts.dong_jie_yajin,0)) + sum(ifnull(ts.yajin,0))) num from tp_member ts ');
        $sum2 = M('')->query('select sum(ifnull(t.amount,0)) num from tp_yajin_log t where t.type = 3 ');
        $res['num'] = floatval($sum1[0]['num']) - floatval($sum2[0]['num']);
        if(IS_AJAX){
            $this->ajaxReturn($res);
        }else{
            $this->assign('res', $res);
            $this->display();
        }
    }

    /**
     * 购买抽成记录
     */
    public function chouChengJiLu(){
        $time = I('time');
        $where = '';
        $where1 = '';
        if(!empty($time) && undefined != $time){
            $times = explode('~',$time);
            $where .= ' and STR_TO_DATE(ts.create_time,"%Y-%m-%d")>= "'.$times[0].'" and STR_TO_DATE(ts.create_time,"%Y-%m-%d")<= "'.$times[1].'"';
            $where1.= ' and STR_TO_DATE(create_time,"%Y-%m-%d")>= "'.$times[0].'" and STR_TO_DATE(create_time,"%Y-%m-%d")<= "'.$times[1].'"';
        }
        $model = M("ShopOrder");
        $res['count'] = $model->where('pingtaifei>0'.$where1)->count();
        $page = $_GET['page']?$_GET['page']:1;
        $limit = $_GET['limit']?$_GET['limit']:10;
        $list = M('')->query('select *,ts.id oid,ts.status ostatus,ts.create_time ocreatetime from tp_shop_order ts left join tp_member m on m.uid = ts.uid where ts.pingtaifei > 0 '.$where.' order by ts.id desc');
        $res['list']= $list;
        //充值记录统计
        $sum = M('')->query('select sum(ifnull(ts.pingtaifei,0)) num from tp_shop_order ts where ts.pingtaifei > 0 '.$where);
        $res['num'] = $sum[0]['num'];
        if(IS_AJAX){
            $this->ajaxReturn($res);
        }else{
            $this->assign('res', $res);
            $this->display();
        }
    }

    public function SaveViaTempFile($objWriter)
    {
        $filePath = '' . rand(0, getrandmax()) . rand(0, getrandmax()) . '.tmp';
        $objWriter->save($filePath);
        readfile($filePath);
        unlink($filePath);
    }

    /**
     * 查看图书
     */
    public function edit(){
        $booksM = M("Books");
        $id = $_GET['id'];
        $list = $booksM->field('tp_books.*, tp_member.*,FROM_UNIXTIME(tp_books.add_time,"%m-%d %H:%i") time')->join('tp_member on tp_books.uid = tp_member.uid','left')->where(['tp_books.id' => $id])->find();
        $this->assign('list', $list);
        $this->display();
    }

    /**
     * 删除图书
     */
    public function del(){
        $id = I('id');
        if(empty($id)){
           $this->ajaxReturn(0);
        }
        $result = M('Books')->where('id = '.$id)->delete();
        if($result){
            $this->ajaxReturn(1);
        }else{
            $this->ajaxReturn(0);
        }
    }
}